﻿<?xml version="1.0" encoding="utf-8" ?>
<?xml-stylesheet type='text/xsl' href="scriptsql.xsl"?>
<root xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database includeDrop="true">CBRSys</database>
<procedures>
		
		<procedure owner="dbo" name="Instructor_Get_List" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets all records from the Instructor table
-- Table Comment: An Instructor is someone who is tasked with teaching Sections and coordinating CourseOfferings.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				SELECT
					[InstructorID],
					[FirstName],
					[LastName],
					[Active]
				FROM
					[dbo].[Instructor]
					
				SELECT @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="Instructor_GetPaged" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the Instructor table passing page index and page count parameters
-- Table Comment: An Instructor is someone who is tasked with teaching Sections and coordinating CourseOfferings.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[InstructorID]'
				END

				-- SQL Server 2005 Paging
				DECLARE @SQL AS nvarchar(MAX)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [InstructorID]'
				SET @SQL = @SQL + ', [FirstName]'
				SET @SQL = @SQL + ', [LastName]'
				SET @SQL = @SQL + ', [Active]'
				SET @SQL = @SQL + ' FROM [dbo].[Instructor]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [InstructorID],'
				SET @SQL = @SQL + ' [FirstName],'
				SET @SQL = @SQL + ' [LastName],'
				SET @SQL = @SQL + ' [Active]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				EXEC sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Instructor]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="Instructor_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Inserts a record into the Instructor table
-- Table Comment: An Instructor is someone who is tasked with teaching Sections and coordinating CourseOfferings.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@InstructorId" type="int" direction="Output" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@FirstName" type="varchar" direction="Input" size="30" precision="0" scale="0" param="(30)" nulldefault=""/>
				<parameter name="@LastName" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
				<parameter name="@Active" type="bit" direction="Input" size="1" precision="1" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				INSERT INTO [dbo].[Instructor]
					(
					[FirstName]
					,[LastName]
					,[Active]
					)
				VALUES
					(
					@FirstName
					,@LastName
					,@Active
					)
				
				-- Get the identity value
				SET @InstructorId = SCOPE_IDENTITY()
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="Instructor_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Updates a record in the Instructor table
-- Table Comment: An Instructor is someone who is tasked with teaching Sections and coordinating CourseOfferings.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@InstructorId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@FirstName" type="varchar" direction="Input" size="30" precision="0" scale="0" param="(30)" nulldefault=""/>
				<parameter name="@LastName" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
				<parameter name="@Active" type="bit" direction="Input" size="1" precision="1" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Instructor]
				SET
					[FirstName] = @FirstName
					,[LastName] = @LastName
					,[Active] = @Active
				WHERE
[InstructorID] = @InstructorId 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="Instructor_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Deletes a record in the Instructor table
-- Table Comment: An Instructor is someone who is tasked with teaching Sections and coordinating CourseOfferings.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@InstructorId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[Instructor] WITH (ROWLOCK) 
				WHERE
					[InstructorID] = @InstructorId
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="Instructor_GetByInstructorId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the Instructor table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@InstructorId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[InstructorID],
					[FirstName],
					[LastName],
					[Active]
				FROM
					[dbo].[Instructor]
				WHERE
					[InstructorID] = @InstructorId
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="Instructor_GetByFirstName" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the Instructor table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@FirstName" type="varchar" direction="Input" size="30" precision="0" scale="0" param="(30)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[InstructorID],
					[FirstName],
					[LastName],
					[Active]
				FROM
					[dbo].[Instructor]
				WHERE
					[FirstName] = @FirstName
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="Instructor_GetByLastName" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the Instructor table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@LastName" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[InstructorID],
					[FirstName],
					[LastName],
					[Active]
				FROM
					[dbo].[Instructor]
				WHERE
					[LastName] = @LastName
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="Instructor_Find" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Finds records in the Instructor table passing nullable parameters
-- Table Comment: An Instructor is someone who is tasked with teaching Sections and coordinating CourseOfferings.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@InstructorId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@FirstName" type="varchar" direction="Input" size="30" precision="0" scale="0" param="(30)" nulldefault="null"/>
				<parameter name="@LastName" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault="null"/>
				<parameter name="@Active" type="bit" direction="Input" size="1" precision="1" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [InstructorID]
	, [FirstName]
	, [LastName]
	, [Active]
    FROM
	[dbo].[Instructor]
    WHERE 
	 ([InstructorID] = @InstructorId OR @InstructorId IS NULL)
	AND ([FirstName] = @FirstName OR @FirstName IS NULL)
	AND ([LastName] = @LastName OR @LastName IS NULL)
	AND ([Active] = @Active OR @Active IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [InstructorID]
	, [FirstName]
	, [LastName]
	, [Active]
    FROM
	[dbo].[Instructor]
    WHERE 
	 ([InstructorID] = @InstructorId AND @InstructorId is not null)
	OR ([FirstName] = @FirstName AND @FirstName is not null)
	OR ([LastName] = @LastName AND @LastName is not null)
	OR ([Active] = @Active AND @Active is not null)
	SELECT @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="Instructor_Find_Dynamic"  skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the Instructor table using a dynamically generated query.
-- Table Comment: An Instructor is someone who is tasked with teaching Sections and coordinating CourseOfferings.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				BEGIN
				
				WITH PageIndex AS (
					SELECT TOP {3} row_number() over (order by {1}) AS RowIndex
					, [InstructorID]
					, [FirstName]
					, [LastName]
					, [Active]
					FROM [dbo].[Instructor] {0}
				)
				SELECT
				       [InstructorID],
				       [FirstName],
				       [LastName],
				       [Active]
				  FROM PageIndex
				 WHERE RowIndex > {2}
				   AND RowIndex <= {3}
				ORDER BY {1};
				

				-- get total count
				SELECT COUNT(*) AS TotalRowCount FROM [dbo].[Instructor] {0};
				
				END
			]]></body>
		</procedure>
		
				
		<procedure owner="dbo" name="Instructor_ListInactive"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
			</parameters>
			<body><![CDATA[
				


SELECT
	[InstructorID],
	[FirstName],
	[LastName],
	[Active]
FROM Instructor
WHERE [Active] = 0

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="Instructor_Activate"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@InstructorID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


UPDATE Instructor SET
    [Active] = 1
WHERE
	[InstructorID] = @InstructorID

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="Instructor_LookupByPartialLastName"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@LastName" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	[InstructorID],
	[FirstName],
	[LastName],
	[Active]
FROM Instructor
WHERE
	[LastName] Like '%' + @LastName + '%'
	

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="Instructor_ListActive"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
			</parameters>
			<body><![CDATA[
				


SELECT
	[InstructorID],
	[FirstName],
	[LastName],
	[Active]
FROM Instructor
WHERE Active = 1

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="Instructor_Deactivate"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@InstructorID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


UPDATE Instructor SET
    [Active] = 0
WHERE
	[InstructorID] = @InstructorID

			]]></body>
					</procedure>
		
		<procedure owner="dbo" name="ComponentType_Get_List" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets all records from the ComponentType table
-- Table Comment: A ComponentType is a classification for grouping related EvaluationComponents by type.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				SELECT
					[ComponentTypeID],
					[Name],
					[Description]
				FROM
					[dbo].[ComponentType]
					
				SELECT @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="ComponentType_GetPaged" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the ComponentType table passing page index and page count parameters
-- Table Comment: A ComponentType is a classification for grouping related EvaluationComponents by type.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[ComponentTypeID]'
				END

				-- SQL Server 2005 Paging
				DECLARE @SQL AS nvarchar(MAX)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [ComponentTypeID]'
				SET @SQL = @SQL + ', [Name]'
				SET @SQL = @SQL + ', [Description]'
				SET @SQL = @SQL + ' FROM [dbo].[ComponentType]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [ComponentTypeID],'
				SET @SQL = @SQL + ' [Name],'
				SET @SQL = @SQL + ' [Description]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				EXEC sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[ComponentType]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="ComponentType_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Inserts a record into the ComponentType table
-- Table Comment: A ComponentType is a classification for grouping related EvaluationComponents by type.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@ComponentTypeId" type="int" direction="Output" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Name" type="varchar" direction="Input" size="10" precision="0" scale="0" param="(10)" nulldefault=""/>
				<parameter name="@Description" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				INSERT INTO [dbo].[ComponentType]
					(
					[Name]
					,[Description]
					)
				VALUES
					(
					@Name
					,@Description
					)
				
				-- Get the identity value
				SET @ComponentTypeId = SCOPE_IDENTITY()
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="ComponentType_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Updates a record in the ComponentType table
-- Table Comment: A ComponentType is a classification for grouping related EvaluationComponents by type.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@ComponentTypeId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Name" type="varchar" direction="Input" size="10" precision="0" scale="0" param="(10)" nulldefault=""/>
				<parameter name="@Description" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[ComponentType]
				SET
					[Name] = @Name
					,[Description] = @Description
				WHERE
[ComponentTypeID] = @ComponentTypeId 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="ComponentType_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Deletes a record in the ComponentType table
-- Table Comment: A ComponentType is a classification for grouping related EvaluationComponents by type.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@ComponentTypeId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[ComponentType] WITH (ROWLOCK) 
				WHERE
					[ComponentTypeID] = @ComponentTypeId
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="ComponentType_GetByComponentTypeId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the ComponentType table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@ComponentTypeId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[ComponentTypeID],
					[Name],
					[Description]
				FROM
					[dbo].[ComponentType]
				WHERE
					[ComponentTypeID] = @ComponentTypeId
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="ComponentType_GetByName" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the ComponentType table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Name" type="varchar" direction="Input" size="10" precision="0" scale="0" param="(10)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[ComponentTypeID],
					[Name],
					[Description]
				FROM
					[dbo].[ComponentType]
				WHERE
					[Name] = @Name
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="ComponentType_Find" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Finds records in the ComponentType table passing nullable parameters
-- Table Comment: A ComponentType is a classification for grouping related EvaluationComponents by type.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@ComponentTypeId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@Name" type="varchar" direction="Input" size="10" precision="0" scale="0" param="(10)" nulldefault="null"/>
				<parameter name="@Description" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [ComponentTypeID]
	, [Name]
	, [Description]
    FROM
	[dbo].[ComponentType]
    WHERE 
	 ([ComponentTypeID] = @ComponentTypeId OR @ComponentTypeId IS NULL)
	AND ([Name] = @Name OR @Name IS NULL)
	AND ([Description] = @Description OR @Description IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [ComponentTypeID]
	, [Name]
	, [Description]
    FROM
	[dbo].[ComponentType]
    WHERE 
	 ([ComponentTypeID] = @ComponentTypeId AND @ComponentTypeId is not null)
	OR ([Name] = @Name AND @Name is not null)
	OR ([Description] = @Description AND @Description is not null)
	SELECT @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="ComponentType_Find_Dynamic"  skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the ComponentType table using a dynamically generated query.
-- Table Comment: A ComponentType is a classification for grouping related EvaluationComponents by type.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				BEGIN
				
				WITH PageIndex AS (
					SELECT TOP {3} row_number() over (order by {1}) AS RowIndex
					, [ComponentTypeID]
					, [Name]
					, [Description]
					FROM [dbo].[ComponentType] {0}
				)
				SELECT
				       [ComponentTypeID],
				       [Name],
				       [Description]
				  FROM PageIndex
				 WHERE RowIndex > {2}
				   AND RowIndex <= {3}
				ORDER BY {1};
				

				-- get total count
				SELECT COUNT(*) AS TotalRowCount FROM [dbo].[ComponentType] {0};
				
				END
			]]></body>
		</procedure>
		
				
		<procedure owner="dbo" name="ComponentType_LookupByName"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@Name" type="varchar" direction="Input" size="10" precision="0" scale="0" param="(10)" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	[ComponentTypeID],
	[Name],
	[Description]
FROM ComponentType
WHERE
	[Name] = @Name
	

			]]></body>
					</procedure>
		
		<procedure owner="dbo" name="Section_Get_List" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets all records from the Section table
-- Table Comment: A Section acts to subdivide a CourseOffering into individual classes; Sections can also serve to associate students into common classes for different courses.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				SELECT
					[SectionID],
					[CourseOfferingID],
					[Name],
					[InstructorID]
				FROM
					[dbo].[Section]
					
				SELECT @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="Section_GetPaged" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the Section table passing page index and page count parameters
-- Table Comment: A Section acts to subdivide a CourseOffering into individual classes; Sections can also serve to associate students into common classes for different courses.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[SectionID]'
				END

				-- SQL Server 2005 Paging
				DECLARE @SQL AS nvarchar(MAX)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [SectionID]'
				SET @SQL = @SQL + ', [CourseOfferingID]'
				SET @SQL = @SQL + ', [Name]'
				SET @SQL = @SQL + ', [InstructorID]'
				SET @SQL = @SQL + ' FROM [dbo].[Section]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [SectionID],'
				SET @SQL = @SQL + ' [CourseOfferingID],'
				SET @SQL = @SQL + ' [Name],'
				SET @SQL = @SQL + ' [InstructorID]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				EXEC sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Section]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="Section_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Inserts a record into the Section table
-- Table Comment: A Section acts to subdivide a CourseOffering into individual classes; Sections can also serve to associate students into common classes for different courses.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SectionId" type="int" direction="Output" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@CourseOfferingId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Name" type="char" direction="Input" size="3" precision="0" scale="0" param="(3)" nulldefault=""/>
				<parameter name="@InstructorId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				INSERT INTO [dbo].[Section]
					(
					[CourseOfferingID]
					,[Name]
					,[InstructorID]
					)
				VALUES
					(
					@CourseOfferingId
					,@Name
					,@InstructorId
					)
				
				-- Get the identity value
				SET @SectionId = SCOPE_IDENTITY()
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="Section_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Updates a record in the Section table
-- Table Comment: A Section acts to subdivide a CourseOffering into individual classes; Sections can also serve to associate students into common classes for different courses.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SectionId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@CourseOfferingId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Name" type="char" direction="Input" size="3" precision="0" scale="0" param="(3)" nulldefault=""/>
				<parameter name="@InstructorId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Section]
				SET
					[CourseOfferingID] = @CourseOfferingId
					,[Name] = @Name
					,[InstructorID] = @InstructorId
				WHERE
[SectionID] = @SectionId 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="Section_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Deletes a record in the Section table
-- Table Comment: A Section acts to subdivide a CourseOffering into individual classes; Sections can also serve to associate students into common classes for different courses.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SectionId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[Section] WITH (ROWLOCK) 
				WHERE
					[SectionID] = @SectionId
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="Section_GetByInstructorId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the Section table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@InstructorId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				
				SELECT
					[SectionID],
					[CourseOfferingID],
					[Name],
					[InstructorID]
				FROM
					[dbo].[Section]
				WHERE
					[InstructorID] = @InstructorId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="Section_GetByCourseOfferingId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the Section table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@CourseOfferingId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				
				SELECT
					[SectionID],
					[CourseOfferingID],
					[Name],
					[InstructorID]
				FROM
					[dbo].[Section]
				WHERE
					[CourseOfferingID] = @CourseOfferingId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="Section_GetBySectionId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the Section table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SectionId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[SectionID],
					[CourseOfferingID],
					[Name],
					[InstructorID]
				FROM
					[dbo].[Section]
				WHERE
					[SectionID] = @SectionId
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="Section_Find" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Finds records in the Section table passing nullable parameters
-- Table Comment: A Section acts to subdivide a CourseOffering into individual classes; Sections can also serve to associate students into common classes for different courses.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@SectionId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@CourseOfferingId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@Name" type="char" direction="Input" size="3" precision="0" scale="0" param="(3)" nulldefault="null"/>
				<parameter name="@InstructorId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [SectionID]
	, [CourseOfferingID]
	, [Name]
	, [InstructorID]
    FROM
	[dbo].[Section]
    WHERE 
	 ([SectionID] = @SectionId OR @SectionId IS NULL)
	AND ([CourseOfferingID] = @CourseOfferingId OR @CourseOfferingId IS NULL)
	AND ([Name] = @Name OR @Name IS NULL)
	AND ([InstructorID] = @InstructorId OR @InstructorId IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [SectionID]
	, [CourseOfferingID]
	, [Name]
	, [InstructorID]
    FROM
	[dbo].[Section]
    WHERE 
	 ([SectionID] = @SectionId AND @SectionId is not null)
	OR ([CourseOfferingID] = @CourseOfferingId AND @CourseOfferingId is not null)
	OR ([Name] = @Name AND @Name is not null)
	OR ([InstructorID] = @InstructorId AND @InstructorId is not null)
	SELECT @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="Section_Find_Dynamic"  skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the Section table using a dynamically generated query.
-- Table Comment: A Section acts to subdivide a CourseOffering into individual classes; Sections can also serve to associate students into common classes for different courses.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				BEGIN
				
				WITH PageIndex AS (
					SELECT TOP {3} row_number() over (order by {1}) AS RowIndex
					, [SectionID]
					, [CourseOfferingID]
					, [Name]
					, [InstructorID]
					FROM [dbo].[Section] {0}
				)
				SELECT
				       [SectionID],
				       [CourseOfferingID],
				       [Name],
				       [InstructorID]
				  FROM PageIndex
				 WHERE RowIndex > {2}
				   AND RowIndex <= {3}
				ORDER BY {1};
				

				-- get total count
				SELECT COUNT(*) AS TotalRowCount FROM [dbo].[Section] {0};
				
				END
			]]></body>
		</procedure>
		
				
		<procedure owner="dbo" name="Section_LookupActiveByCourseOffering"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@CourseOfferingID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	SO.[SectionID],
	SO.[CourseOfferingID],
	SO.[Name],
	SO.[InstructorID]
FROM SectionOffering SO
INNER JOIN CourseOffering CO ON CO.[CourseOfferingID] = SO.[CourseOfferingID]
WHERE
	[CourseOfferingID]=@CourseOfferingID
AND [Active] = 1

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="Section_ListAllInactive"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
			</parameters>
			<body><![CDATA[
				


SELECT
	SO.[SectionID],
	SO.[CourseOfferingID],
	SO.[Name],
	SO.[InstructorID]
FROM SectionOffering SO
INNER JOIN CourseOffering CO ON CO.[CourseOfferingID] = SO.[CourseOfferingID]
WHERE [Active] = 0

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="Section_ListAllActive"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
			</parameters>
			<body><![CDATA[
				


SELECT
	SO.[SectionID],
	SO.[CourseOfferingID],
	SO.[Name],
	SO.[InstructorID]
FROM SectionOffering SO
INNER JOIN CourseOffering CO ON CO.[CourseOfferingID] = SO.[CourseOfferingID]
WHERE [Active] = 1

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="Section_LookupActiveByTerm"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@Term" type="tinyint" direction="Input" size="1" precision="3" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	SO.[SectionID],
	SO.[CourseOfferingID],
	SO.[Name],
	SO.[InstructorID]
FROM SectionOffering SO
INNER JOIN CourseOffering CO ON SO.CourseOfferingID = CO.CourseOfferingID
INNER JOIN Course C ON CO.CourseID = C.CourseID
WHERE
	C.[Term]=@Term
AND CO.[Active] = 1

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="Section_GetSize"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@SectionID" type="bigint" direction="Input" size="8" precision="19" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	Count(SectionID)
FROM EnrolledStudent ES
WHERE
	[SectionID] = @SectionID
	

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="Section_LookupByTerm"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@Term" type="tinyint" direction="Input" size="1" precision="3" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	SO.[SectionID],
	SO.[CourseOfferingID],
	SO.[Name],
	SO.[InstructorID]
FROM SectionOffering SO
INNER JOIN CourseOffering CO ON SO.CourseOfferingID = CO.CourseOfferingID
INNER JOIN Course C ON CO.CourseID = C.CourseID
WHERE
	C.[Term]=@Term

			]]></body>
					</procedure>
		
		<procedure owner="dbo" name="EvaluationComponent_Get_List" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets all records from the EvaluationComponent table
-- Table Comment: An EvaluationComponent is some item, such as an exam or a lab, that is used to evaluate and measure student’s progress through a course.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				SELECT
					[EvaluationComponentID],
					[CourseOfferingID],
					[ComponentTypeID],
					[Description],
					[RelativeWeight],
					[TotalPossibleMarks],
					[OrderOfDelivery]
				FROM
					[dbo].[EvaluationComponent]
					
				SELECT @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="EvaluationComponent_GetPaged" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the EvaluationComponent table passing page index and page count parameters
-- Table Comment: An EvaluationComponent is some item, such as an exam or a lab, that is used to evaluate and measure student’s progress through a course.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[EvaluationComponentID]'
				END

				-- SQL Server 2005 Paging
				DECLARE @SQL AS nvarchar(MAX)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [EvaluationComponentID]'
				SET @SQL = @SQL + ', [CourseOfferingID]'
				SET @SQL = @SQL + ', [ComponentTypeID]'
				SET @SQL = @SQL + ', [Description]'
				SET @SQL = @SQL + ', [RelativeWeight]'
				SET @SQL = @SQL + ', [TotalPossibleMarks]'
				SET @SQL = @SQL + ', [OrderOfDelivery]'
				SET @SQL = @SQL + ' FROM [dbo].[EvaluationComponent]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [EvaluationComponentID],'
				SET @SQL = @SQL + ' [CourseOfferingID],'
				SET @SQL = @SQL + ' [ComponentTypeID],'
				SET @SQL = @SQL + ' [Description],'
				SET @SQL = @SQL + ' [RelativeWeight],'
				SET @SQL = @SQL + ' [TotalPossibleMarks],'
				SET @SQL = @SQL + ' [OrderOfDelivery]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				EXEC sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[EvaluationComponent]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="EvaluationComponent_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Inserts a record into the EvaluationComponent table
-- Table Comment: An EvaluationComponent is some item, such as an exam or a lab, that is used to evaluate and measure student’s progress through a course.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@EvaluationComponentId" type="int" direction="Output" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@CourseOfferingId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@ComponentTypeId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Description" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
				<parameter name="@RelativeWeight" type="tinyint" direction="Input" size="1" precision="3" scale="0" param="" nulldefault=""/>
				<parameter name="@TotalPossibleMarks" type="decimal" direction="Input" size="9" precision="18" scale="0" param="(18, 0)" nulldefault=""/>
				<parameter name="@OrderOfDelivery" type="smallint" direction="Input" size="2" precision="5" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				INSERT INTO [dbo].[EvaluationComponent]
					(
					[CourseOfferingID]
					,[ComponentTypeID]
					,[Description]
					,[RelativeWeight]
					,[TotalPossibleMarks]
					,[OrderOfDelivery]
					)
				VALUES
					(
					@CourseOfferingId
					,@ComponentTypeId
					,@Description
					,@RelativeWeight
					,@TotalPossibleMarks
					,@OrderOfDelivery
					)
				
				-- Get the identity value
				SET @EvaluationComponentId = SCOPE_IDENTITY()
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="EvaluationComponent_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Updates a record in the EvaluationComponent table
-- Table Comment: An EvaluationComponent is some item, such as an exam or a lab, that is used to evaluate and measure student’s progress through a course.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@EvaluationComponentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@CourseOfferingId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@ComponentTypeId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Description" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
				<parameter name="@RelativeWeight" type="tinyint" direction="Input" size="1" precision="3" scale="0" param="" nulldefault=""/>
				<parameter name="@TotalPossibleMarks" type="decimal" direction="Input" size="9" precision="18" scale="0" param="(18, 0)" nulldefault=""/>
				<parameter name="@OrderOfDelivery" type="smallint" direction="Input" size="2" precision="5" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[EvaluationComponent]
				SET
					[CourseOfferingID] = @CourseOfferingId
					,[ComponentTypeID] = @ComponentTypeId
					,[Description] = @Description
					,[RelativeWeight] = @RelativeWeight
					,[TotalPossibleMarks] = @TotalPossibleMarks
					,[OrderOfDelivery] = @OrderOfDelivery
				WHERE
[EvaluationComponentID] = @EvaluationComponentId 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="EvaluationComponent_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Deletes a record in the EvaluationComponent table
-- Table Comment: An EvaluationComponent is some item, such as an exam or a lab, that is used to evaluate and measure student’s progress through a course.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@EvaluationComponentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[EvaluationComponent] WITH (ROWLOCK) 
				WHERE
					[EvaluationComponentID] = @EvaluationComponentId
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="EvaluationComponent_GetByCourseOfferingId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the EvaluationComponent table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@CourseOfferingId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				
				SELECT
					[EvaluationComponentID],
					[CourseOfferingID],
					[ComponentTypeID],
					[Description],
					[RelativeWeight],
					[TotalPossibleMarks],
					[OrderOfDelivery]
				FROM
					[dbo].[EvaluationComponent]
				WHERE
					[CourseOfferingID] = @CourseOfferingId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="EvaluationComponent_GetByComponentTypeId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the EvaluationComponent table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@ComponentTypeId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				
				SELECT
					[EvaluationComponentID],
					[CourseOfferingID],
					[ComponentTypeID],
					[Description],
					[RelativeWeight],
					[TotalPossibleMarks],
					[OrderOfDelivery]
				FROM
					[dbo].[EvaluationComponent]
				WHERE
					[ComponentTypeID] = @ComponentTypeId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="EvaluationComponent_GetByEvaluationComponentId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the EvaluationComponent table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@EvaluationComponentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[EvaluationComponentID],
					[CourseOfferingID],
					[ComponentTypeID],
					[Description],
					[RelativeWeight],
					[TotalPossibleMarks],
					[OrderOfDelivery]
				FROM
					[dbo].[EvaluationComponent]
				WHERE
					[EvaluationComponentID] = @EvaluationComponentId
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="EvaluationComponent_Find" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Finds records in the EvaluationComponent table passing nullable parameters
-- Table Comment: An EvaluationComponent is some item, such as an exam or a lab, that is used to evaluate and measure student’s progress through a course.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@EvaluationComponentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@CourseOfferingId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@ComponentTypeId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@Description" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault="null"/>
				<parameter name="@RelativeWeight" type="tinyint" direction="Input" size="1" precision="3" scale="0" param="" nulldefault="null"/>
				<parameter name="@TotalPossibleMarks" type="decimal" direction="Input" size="9" precision="18" scale="0" param="(18, 0)" nulldefault="null"/>
				<parameter name="@OrderOfDelivery" type="smallint" direction="Input" size="2" precision="5" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [EvaluationComponentID]
	, [CourseOfferingID]
	, [ComponentTypeID]
	, [Description]
	, [RelativeWeight]
	, [TotalPossibleMarks]
	, [OrderOfDelivery]
    FROM
	[dbo].[EvaluationComponent]
    WHERE 
	 ([EvaluationComponentID] = @EvaluationComponentId OR @EvaluationComponentId IS NULL)
	AND ([CourseOfferingID] = @CourseOfferingId OR @CourseOfferingId IS NULL)
	AND ([ComponentTypeID] = @ComponentTypeId OR @ComponentTypeId IS NULL)
	AND ([Description] = @Description OR @Description IS NULL)
	AND ([RelativeWeight] = @RelativeWeight OR @RelativeWeight IS NULL)
	AND ([TotalPossibleMarks] = @TotalPossibleMarks OR @TotalPossibleMarks IS NULL)
	AND ([OrderOfDelivery] = @OrderOfDelivery OR @OrderOfDelivery IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [EvaluationComponentID]
	, [CourseOfferingID]
	, [ComponentTypeID]
	, [Description]
	, [RelativeWeight]
	, [TotalPossibleMarks]
	, [OrderOfDelivery]
    FROM
	[dbo].[EvaluationComponent]
    WHERE 
	 ([EvaluationComponentID] = @EvaluationComponentId AND @EvaluationComponentId is not null)
	OR ([CourseOfferingID] = @CourseOfferingId AND @CourseOfferingId is not null)
	OR ([ComponentTypeID] = @ComponentTypeId AND @ComponentTypeId is not null)
	OR ([Description] = @Description AND @Description is not null)
	OR ([RelativeWeight] = @RelativeWeight AND @RelativeWeight is not null)
	OR ([TotalPossibleMarks] = @TotalPossibleMarks AND @TotalPossibleMarks is not null)
	OR ([OrderOfDelivery] = @OrderOfDelivery AND @OrderOfDelivery is not null)
	SELECT @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="EvaluationComponent_Find_Dynamic"  skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the EvaluationComponent table using a dynamically generated query.
-- Table Comment: An EvaluationComponent is some item, such as an exam or a lab, that is used to evaluate and measure student’s progress through a course.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				BEGIN
				
				WITH PageIndex AS (
					SELECT TOP {3} row_number() over (order by {1}) AS RowIndex
					, [EvaluationComponentID]
					, [CourseOfferingID]
					, [ComponentTypeID]
					, [Description]
					, [RelativeWeight]
					, [TotalPossibleMarks]
					, [OrderOfDelivery]
					FROM [dbo].[EvaluationComponent] {0}
				)
				SELECT
				       [EvaluationComponentID],
				       [CourseOfferingID],
				       [ComponentTypeID],
				       [Description],
				       [RelativeWeight],
				       [TotalPossibleMarks],
				       [OrderOfDelivery]
				  FROM PageIndex
				 WHERE RowIndex > {2}
				   AND RowIndex <= {3}
				ORDER BY {1};
				

				-- get total count
				SELECT COUNT(*) AS TotalRowCount FROM [dbo].[EvaluationComponent] {0};
				
				END
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="Student_Get_List" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets all records from the Student table
-- Table Comment: A Student is someone who may enroll in programs of study.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				SELECT
					[StudentID],
					[GivenSchoolID],
					[FirstName],
					[LastName],
					[Gender]
				FROM
					[dbo].[Student]
					
				SELECT @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="Student_GetPaged" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the Student table passing page index and page count parameters
-- Table Comment: A Student is someone who may enroll in programs of study.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[StudentID]'
				END

				-- SQL Server 2005 Paging
				DECLARE @SQL AS nvarchar(MAX)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [StudentID]'
				SET @SQL = @SQL + ', [GivenSchoolID]'
				SET @SQL = @SQL + ', [FirstName]'
				SET @SQL = @SQL + ', [LastName]'
				SET @SQL = @SQL + ', [Gender]'
				SET @SQL = @SQL + ' FROM [dbo].[Student]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [StudentID],'
				SET @SQL = @SQL + ' [GivenSchoolID],'
				SET @SQL = @SQL + ' [FirstName],'
				SET @SQL = @SQL + ' [LastName],'
				SET @SQL = @SQL + ' [Gender]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				EXEC sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Student]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="Student_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Inserts a record into the Student table
-- Table Comment: A Student is someone who may enroll in programs of study.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@StudentId" type="int" direction="Output" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@GivenSchoolId" type="varchar" direction="Input" size="15" precision="0" scale="0" param="(15)" nulldefault=""/>
				<parameter name="@FirstName" type="varchar" direction="Input" size="30" precision="0" scale="0" param="(30)" nulldefault=""/>
				<parameter name="@LastName" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
				<parameter name="@Gender" type="char" direction="Input" size="1" precision="0" scale="0" param="(1)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				INSERT INTO [dbo].[Student]
					(
					[GivenSchoolID]
					,[FirstName]
					,[LastName]
					,[Gender]
					)
				VALUES
					(
					@GivenSchoolId
					,@FirstName
					,@LastName
					,@Gender
					)
				
				-- Get the identity value
				SET @StudentId = SCOPE_IDENTITY()
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="Student_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Updates a record in the Student table
-- Table Comment: A Student is someone who may enroll in programs of study.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@StudentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@GivenSchoolId" type="varchar" direction="Input" size="15" precision="0" scale="0" param="(15)" nulldefault=""/>
				<parameter name="@FirstName" type="varchar" direction="Input" size="30" precision="0" scale="0" param="(30)" nulldefault=""/>
				<parameter name="@LastName" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
				<parameter name="@Gender" type="char" direction="Input" size="1" precision="0" scale="0" param="(1)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Student]
				SET
					[GivenSchoolID] = @GivenSchoolId
					,[FirstName] = @FirstName
					,[LastName] = @LastName
					,[Gender] = @Gender
				WHERE
[StudentID] = @StudentId 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="Student_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Deletes a record in the Student table
-- Table Comment: A Student is someone who may enroll in programs of study.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@StudentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[Student] WITH (ROWLOCK) 
				WHERE
					[StudentID] = @StudentId
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="Student_GetByStudentId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the Student table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@StudentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[StudentID],
					[GivenSchoolID],
					[FirstName],
					[LastName],
					[Gender]
				FROM
					[dbo].[Student]
				WHERE
					[StudentID] = @StudentId
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="Student_GetByGivenSchoolId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the Student table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@GivenSchoolId" type="varchar" direction="Input" size="15" precision="0" scale="0" param="(15)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[StudentID],
					[GivenSchoolID],
					[FirstName],
					[LastName],
					[Gender]
				FROM
					[dbo].[Student]
				WHERE
					[GivenSchoolID] = @GivenSchoolId
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="Student_GetByLastName" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the Student table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@LastName" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[StudentID],
					[GivenSchoolID],
					[FirstName],
					[LastName],
					[Gender]
				FROM
					[dbo].[Student]
				WHERE
					[LastName] = @LastName
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="Student_GetByFirstName" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the Student table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@FirstName" type="varchar" direction="Input" size="30" precision="0" scale="0" param="(30)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[StudentID],
					[GivenSchoolID],
					[FirstName],
					[LastName],
					[Gender]
				FROM
					[dbo].[Student]
				WHERE
					[FirstName] = @FirstName
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="Student_Find" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Finds records in the Student table passing nullable parameters
-- Table Comment: A Student is someone who may enroll in programs of study.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@StudentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@GivenSchoolId" type="varchar" direction="Input" size="15" precision="0" scale="0" param="(15)" nulldefault="null"/>
				<parameter name="@FirstName" type="varchar" direction="Input" size="30" precision="0" scale="0" param="(30)" nulldefault="null"/>
				<parameter name="@LastName" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault="null"/>
				<parameter name="@Gender" type="char" direction="Input" size="1" precision="0" scale="0" param="(1)" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [StudentID]
	, [GivenSchoolID]
	, [FirstName]
	, [LastName]
	, [Gender]
    FROM
	[dbo].[Student]
    WHERE 
	 ([StudentID] = @StudentId OR @StudentId IS NULL)
	AND ([GivenSchoolID] = @GivenSchoolId OR @GivenSchoolId IS NULL)
	AND ([FirstName] = @FirstName OR @FirstName IS NULL)
	AND ([LastName] = @LastName OR @LastName IS NULL)
	AND ([Gender] = @Gender OR @Gender IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [StudentID]
	, [GivenSchoolID]
	, [FirstName]
	, [LastName]
	, [Gender]
    FROM
	[dbo].[Student]
    WHERE 
	 ([StudentID] = @StudentId AND @StudentId is not null)
	OR ([GivenSchoolID] = @GivenSchoolId AND @GivenSchoolId is not null)
	OR ([FirstName] = @FirstName AND @FirstName is not null)
	OR ([LastName] = @LastName AND @LastName is not null)
	OR ([Gender] = @Gender AND @Gender is not null)
	SELECT @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="Student_Find_Dynamic"  skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the Student table using a dynamically generated query.
-- Table Comment: A Student is someone who may enroll in programs of study.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				BEGIN
				
				WITH PageIndex AS (
					SELECT TOP {3} row_number() over (order by {1}) AS RowIndex
					, [StudentID]
					, [GivenSchoolID]
					, [FirstName]
					, [LastName]
					, [Gender]
					FROM [dbo].[Student] {0}
				)
				SELECT
				       [StudentID],
				       [GivenSchoolID],
				       [FirstName],
				       [LastName],
				       [Gender]
				  FROM PageIndex
				 WHERE RowIndex > {2}
				   AND RowIndex <= {3}
				ORDER BY {1};
				

				-- get total count
				SELECT COUNT(*) AS TotalRowCount FROM [dbo].[Student] {0};
				
				END
			]]></body>
		</procedure>
		
				
		<procedure owner="dbo" name="Student_LookupFinalCourseMark"  skip="true">
			<comment><![CDATA[
-- [dbo].[Student_LookupFinalCourseMark]
-- The FinalCourseMark returned is the highest mark
-- for the given student in the given course
]]></comment>
			<parameters>
				<parameter name="@StudentID" type="bigint" direction="Input" size="8" precision="19" scale="0" param="" nulldefault="null"/>
				<parameter name="@CourseNumber" type="varchar" direction="Input" size="10" precision="0" scale="0" param="(10)" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	MAX(ES.[FinalMarkAwarded])
FROM EnrolledStudent ES
INNER JOIN Student S ON ES.StudentID = S.StudentID
INNER JOIN SectionOffering SO ON SO.SectionID = ES.SectionID
INNER JOIN CourseOffering CO ON CO.CourseOfferingID = SO.CourseOfferingID
INNER JOIN Course C ON C.CourseID = CO.CourseID
WHERE
	S.[StudentID] = @StudentID
AND C.[Number] = @CourseNumber
AND CO.[Active] = 0

	

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="Student_LookupByCourseEligibility"  skip="true">
			<comment><![CDATA[]]></comment>
			<parameters>
				<parameter name="@CourseID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				

-- All students who have 
--		have not passed this course
--	and have passed the prerequisites (if any)
	SELECT	S.StudentID, S.GivenSchoolID, S.FirstName, S.LastName, S.Gender
	FROM	Student S
	LEFT OUTER JOIN EnrolledStudent ES ON ES.StudentID = S.StudentID
	WHERE	S.StudentID NOT IN (
				-- Students who have passed this course
				SELECT ES.StudentID FROM EnrolledStudent ES
				INNER JOIN SectionOffering SO ON ES.SectionID = SO.SectionID
				INNER JOIN CourseOffering CO ON SO.CourseOfferingID = CO.CourseOfferingID
				WHERE	CO.CourseID = @CourseID
				AND		ES.FinalMarkAwarded >= CO.PassMark
				)
	AND		(S.StudentID IN (
				-- Students who have passed all prerequisite
				SELECT ES.StudentID FROM EnrolledStudent ES
				INNER JOIN SectionOffering SO ON ES.SectionID = SO.SectionID
				INNER JOIN CourseOffering CO ON SO.CourseOfferingID = CO.CourseOfferingID
				INNER JOIN Course C ON CO.CourseID = C.CourseID
				INNER JOIN CoursePrerequisite CP ON CP.RequiredCourseID = C.CourseID
				WHERE	CP.PrimaryCourseID = @CourseID
				AND		ES.FinalMarkAwarded >  CO.PassMark
				GROUP BY ES.StudentID
				HAVING COUNT(CP.PrimaryCourseID) = (
													SELECT COUNT(CP.PrimaryCourseID)
													FROM CoursePrerequisite CP
													WHERE	CP.PrimaryCourseID = @CourseID
												   )
				)
			OR ( -- There are no prerequisites
				SELECT COUNT(CP.PrimaryCourseID) 
				FROM CoursePrerequisite CP 
				WHERE	CP.PrimaryCourseID = @CourseID
			   ) = 0
			)
	AND		@CourseID IN (SELECT ID FROM Course)
	GROUP BY S.StudentID, S.GivenSchoolID, S.FirstName, S.LastName, S.Gender
	ORDER BY S.StudentID

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="Student_LookupTranscript"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@StudentID" type="bigint" direction="Input" size="8" precision="19" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	S.[StudentID],
	C.[Number]  CourseNumber,
	C.[Name]  CourseName,
	CO.[PassMark],
	CO.[TermStart],
	SO.[Name]  SectionName,
	'Grade' = CASE 
	    WHEN E.Description = 'Withdrawn' THEN 'W'  
	    WHEN ES.[FinalMarkAwarded] = null THEN '-'  
	    ELSE CAST(ES.[FinalMarkAwarded] AS VARCHAR(4))  
    END
FROM Student S
INNER JOIN EnrolledStudent ES ON ES.StudentID = S.StudentID
INNER JOIN EnrollmentStatus E ON E.EnrollmentStatusID = ES.CurrentStatus
INNER JOIN SectionOffering SO ON SO.SectionID = ES.SectionID
INNER JOIN CourseOffering CO ON CO.CourseOfferingID = SO.CourseOfferingID
INNER JOIN Course C ON C.CourseID = CO.CourseID
WHERE
	S.[StudentID] = @StudentID
AND (E.Description = 'Enrolled' OR E.Description = 'Withdrawn')
AND CO.Active = 0
ORDER BY CO.[TermStart]
	

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="Student_LookupByActiveTerm"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@Term" type="smallint" direction="Input" size="2" precision="5" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
    S.[StudentID],
    S.[GivenSchoolID],
    S.[FirstName],
    S.[LastName],
    S.[Gender]
FROM Student S
INNER JOIN EnrolledStudent ES ON ES.StudentID = S.StudentID
INNER JOIN EnrollmentStatus E ON E.EnrollmentStatusID = ES.CurrentStatus
INNER JOIN SectionOffering SO ON SO.SectionID = ES.SectionID
INNER JOIN CourseOffering CO ON CO.CourseOfferingID = SO.CourseOfferingID
INNER JOIN Course C ON C.CourseID = CO.CourseID
WHERE
	C.[Term] = @Term
AND E.Description = 'Enrolled'
AND CO.[Active] = 1
	

			]]></body>
					</procedure>
		
		<procedure owner="dbo" name="EnrollmentStatus_Get_List" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets all records from the EnrollmentStatus table
-- Table Comment: All allowable enrollment statuses for EnrolledStudents.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				SELECT
					[EnrollmentStatusID],
					[Name],
					[Description]
				FROM
					[dbo].[EnrollmentStatus]
					
				SELECT @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="EnrollmentStatus_GetPaged" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the EnrollmentStatus table passing page index and page count parameters
-- Table Comment: All allowable enrollment statuses for EnrolledStudents.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[EnrollmentStatusID]'
				END

				-- SQL Server 2005 Paging
				DECLARE @SQL AS nvarchar(MAX)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [EnrollmentStatusID]'
				SET @SQL = @SQL + ', [Name]'
				SET @SQL = @SQL + ', [Description]'
				SET @SQL = @SQL + ' FROM [dbo].[EnrollmentStatus]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [EnrollmentStatusID],'
				SET @SQL = @SQL + ' [Name],'
				SET @SQL = @SQL + ' [Description]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				EXEC sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[EnrollmentStatus]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="EnrollmentStatus_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Inserts a record into the EnrollmentStatus table
-- Table Comment: All allowable enrollment statuses for EnrolledStudents.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@EnrollmentStatusId" type="int" direction="Output" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Name" type="varchar" direction="Input" size="20" precision="0" scale="0" param="(20)" nulldefault=""/>
				<parameter name="@Description" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				INSERT INTO [dbo].[EnrollmentStatus]
					(
					[Name]
					,[Description]
					)
				VALUES
					(
					@Name
					,@Description
					)
				
				-- Get the identity value
				SET @EnrollmentStatusId = SCOPE_IDENTITY()
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="EnrollmentStatus_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Updates a record in the EnrollmentStatus table
-- Table Comment: All allowable enrollment statuses for EnrolledStudents.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@EnrollmentStatusId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Name" type="varchar" direction="Input" size="20" precision="0" scale="0" param="(20)" nulldefault=""/>
				<parameter name="@Description" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[EnrollmentStatus]
				SET
					[Name] = @Name
					,[Description] = @Description
				WHERE
[EnrollmentStatusID] = @EnrollmentStatusId 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="EnrollmentStatus_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Deletes a record in the EnrollmentStatus table
-- Table Comment: All allowable enrollment statuses for EnrolledStudents.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@EnrollmentStatusId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[EnrollmentStatus] WITH (ROWLOCK) 
				WHERE
					[EnrollmentStatusID] = @EnrollmentStatusId
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="EnrollmentStatus_GetByEnrollmentStatusId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the EnrollmentStatus table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@EnrollmentStatusId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[EnrollmentStatusID],
					[Name],
					[Description]
				FROM
					[dbo].[EnrollmentStatus]
				WHERE
					[EnrollmentStatusID] = @EnrollmentStatusId
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="EnrollmentStatus_GetByName" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the EnrollmentStatus table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Name" type="varchar" direction="Input" size="20" precision="0" scale="0" param="(20)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[EnrollmentStatusID],
					[Name],
					[Description]
				FROM
					[dbo].[EnrollmentStatus]
				WHERE
					[Name] = @Name
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="EnrollmentStatus_Find" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Finds records in the EnrollmentStatus table passing nullable parameters
-- Table Comment: All allowable enrollment statuses for EnrolledStudents.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@EnrollmentStatusId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@Name" type="varchar" direction="Input" size="20" precision="0" scale="0" param="(20)" nulldefault="null"/>
				<parameter name="@Description" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [EnrollmentStatusID]
	, [Name]
	, [Description]
    FROM
	[dbo].[EnrollmentStatus]
    WHERE 
	 ([EnrollmentStatusID] = @EnrollmentStatusId OR @EnrollmentStatusId IS NULL)
	AND ([Name] = @Name OR @Name IS NULL)
	AND ([Description] = @Description OR @Description IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [EnrollmentStatusID]
	, [Name]
	, [Description]
    FROM
	[dbo].[EnrollmentStatus]
    WHERE 
	 ([EnrollmentStatusID] = @EnrollmentStatusId AND @EnrollmentStatusId is not null)
	OR ([Name] = @Name AND @Name is not null)
	OR ([Description] = @Description AND @Description is not null)
	SELECT @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="EnrollmentStatus_Find_Dynamic"  skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the EnrollmentStatus table using a dynamically generated query.
-- Table Comment: All allowable enrollment statuses for EnrolledStudents.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				BEGIN
				
				WITH PageIndex AS (
					SELECT TOP {3} row_number() over (order by {1}) AS RowIndex
					, [EnrollmentStatusID]
					, [Name]
					, [Description]
					FROM [dbo].[EnrollmentStatus] {0}
				)
				SELECT
				       [EnrollmentStatusID],
				       [Name],
				       [Description]
				  FROM PageIndex
				 WHERE RowIndex > {2}
				   AND RowIndex <= {3}
				ORDER BY {1};
				

				-- get total count
				SELECT COUNT(*) AS TotalRowCount FROM [dbo].[EnrollmentStatus] {0};
				
				END
			]]></body>
		</procedure>
		
				
		<procedure owner="dbo" name="EnrollmentStatus_LookupByName"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@Name" type="varchar" direction="Input" size="20" precision="0" scale="0" param="(20)" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	[EnrollmentStatusID],
	[Name],
	[Description]
FROM EnrollmentStatus
WHERE
	[Name] = @Name
	

			]]></body>
					</procedure>
		
		<procedure owner="dbo" name="Course_Get_List" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets all records from the Course table
-- Table Comment: A Course represents a program of study typically offered in a particular term or semester.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				SELECT
					[CourseID],
					[Number],
					[Name],
					[Term],
					[Active]
				FROM
					[dbo].[Course]
					
				SELECT @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="Course_GetPaged" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the Course table passing page index and page count parameters
-- Table Comment: A Course represents a program of study typically offered in a particular term or semester.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[CourseID]'
				END

				-- SQL Server 2005 Paging
				DECLARE @SQL AS nvarchar(MAX)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [CourseID]'
				SET @SQL = @SQL + ', [Number]'
				SET @SQL = @SQL + ', [Name]'
				SET @SQL = @SQL + ', [Term]'
				SET @SQL = @SQL + ', [Active]'
				SET @SQL = @SQL + ' FROM [dbo].[Course]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [CourseID],'
				SET @SQL = @SQL + ' [Number],'
				SET @SQL = @SQL + ' [Name],'
				SET @SQL = @SQL + ' [Term],'
				SET @SQL = @SQL + ' [Active]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				EXEC sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Course]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="Course_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Inserts a record into the Course table
-- Table Comment: A Course represents a program of study typically offered in a particular term or semester.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@CourseId" type="int" direction="Output" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Number" type="varchar" direction="Input" size="10" precision="0" scale="0" param="(10)" nulldefault=""/>
				<parameter name="@Name" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
				<parameter name="@Term" type="smallint" direction="Input" size="2" precision="5" scale="0" param="" nulldefault=""/>
				<parameter name="@Active" type="bit" direction="Input" size="1" precision="1" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				INSERT INTO [dbo].[Course]
					(
					[Number]
					,[Name]
					,[Term]
					,[Active]
					)
				VALUES
					(
					@Number
					,@Name
					,@Term
					,@Active
					)
				
				-- Get the identity value
				SET @CourseId = SCOPE_IDENTITY()
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="Course_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Updates a record in the Course table
-- Table Comment: A Course represents a program of study typically offered in a particular term or semester.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@CourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@Number" type="varchar" direction="Input" size="10" precision="0" scale="0" param="(10)" nulldefault=""/>
				<parameter name="@Name" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault=""/>
				<parameter name="@Term" type="smallint" direction="Input" size="2" precision="5" scale="0" param="" nulldefault=""/>
				<parameter name="@Active" type="bit" direction="Input" size="1" precision="1" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Course]
				SET
					[Number] = @Number
					,[Name] = @Name
					,[Term] = @Term
					,[Active] = @Active
				WHERE
[CourseID] = @CourseId 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="Course_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Deletes a record in the Course table
-- Table Comment: A Course represents a program of study typically offered in a particular term or semester.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@CourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[Course] WITH (ROWLOCK) 
				WHERE
					[CourseID] = @CourseId
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="Course_GetByCourseId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the Course table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@CourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[CourseID],
					[Number],
					[Name],
					[Term],
					[Active]
				FROM
					[dbo].[Course]
				WHERE
					[CourseID] = @CourseId
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="Course_GetByNumber" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the Course table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Number" type="varchar" direction="Input" size="10" precision="0" scale="0" param="(10)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[CourseID],
					[Number],
					[Name],
					[Term],
					[Active]
				FROM
					[dbo].[Course]
				WHERE
					[Number] = @Number
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="Course_Find" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Finds records in the Course table passing nullable parameters
-- Table Comment: A Course represents a program of study typically offered in a particular term or semester.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@CourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@Number" type="varchar" direction="Input" size="10" precision="0" scale="0" param="(10)" nulldefault="null"/>
				<parameter name="@Name" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault="null"/>
				<parameter name="@Term" type="smallint" direction="Input" size="2" precision="5" scale="0" param="" nulldefault="null"/>
				<parameter name="@Active" type="bit" direction="Input" size="1" precision="1" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [CourseID]
	, [Number]
	, [Name]
	, [Term]
	, [Active]
    FROM
	[dbo].[Course]
    WHERE 
	 ([CourseID] = @CourseId OR @CourseId IS NULL)
	AND ([Number] = @Number OR @Number IS NULL)
	AND ([Name] = @Name OR @Name IS NULL)
	AND ([Term] = @Term OR @Term IS NULL)
	AND ([Active] = @Active OR @Active IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [CourseID]
	, [Number]
	, [Name]
	, [Term]
	, [Active]
    FROM
	[dbo].[Course]
    WHERE 
	 ([CourseID] = @CourseId AND @CourseId is not null)
	OR ([Number] = @Number AND @Number is not null)
	OR ([Name] = @Name AND @Name is not null)
	OR ([Term] = @Term AND @Term is not null)
	OR ([Active] = @Active AND @Active is not null)
	SELECT @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="Course_Find_Dynamic"  skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the Course table using a dynamically generated query.
-- Table Comment: A Course represents a program of study typically offered in a particular term or semester.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				BEGIN
				
				WITH PageIndex AS (
					SELECT TOP {3} row_number() over (order by {1}) AS RowIndex
					, [CourseID]
					, [Number]
					, [Name]
					, [Term]
					, [Active]
					FROM [dbo].[Course] {0}
				)
				SELECT
				       [CourseID],
				       [Number],
				       [Name],
				       [Term],
				       [Active]
				  FROM PageIndex
				 WHERE RowIndex > {2}
				   AND RowIndex <= {3}
				ORDER BY {1};
				

				-- get total count
				SELECT COUNT(*) AS TotalRowCount FROM [dbo].[Course] {0};
				
				END
			]]></body>
		</procedure>
		
				
		<procedure owner="dbo" name="Course_LookupDependantsByRequiredCourse"  skip="true">
			<comment><![CDATA[
-- Course_LookupDependantsByRequiredCourse selects the course information on
-- all the courses that depend on the supplied PrimaryCourseID as their pre-requisite
]]></comment>
			<parameters>
				<parameter name="@RequiredCourseID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	C.[CourseID],
	C.[Number],
	C.[Name],
	C.[Term],
	C.[Active]
FROM Course C
INNER JOIN CoursePrerequisite CP ON C.[CourseID] = CP.[PrimaryCourseID]
WHERE
	CP.[RequiredCourseID]=@RequiredCourseID

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="Course_LookupBySection"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@SectionID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	C.[CourseID],
	C.[Number],
	C.[Name],
	C.[Term],
	C.[Active]
FROM Course C
INNER JOIN CourseOffering CO ON CO.[CourseID] = C.[CourseID]
INNER JOIN Section S ON S.[CourseOfferingID] = CO.[CourseOfferingID]
WHERE
	S.[SectionID]=@SectionID
	

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="Course_LookupPrerequisitesByPrimaryCourse"  skip="true">
			<comment><![CDATA[
-- Course_LookupPrerequisitesByPrimaryCourse selects the course information on
-- all the pre-requisite courses for the supplied PrimaryCourseID
]]></comment>
			<parameters>
				<parameter name="@PrimaryCourseID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	C.[CourseID],
	C.[Number],
	C.[Name],
	C.[Term],
	C.[Active]
FROM Course C
INNER JOIN CoursePrerequisite CP ON C.[CourseID] = CP.[RequiredCourseID]
WHERE
	CP.[PrimaryCourseID]=@PrimaryCourseID

			]]></body>
					</procedure>
		
		<procedure owner="dbo" name="CourseOffering_Get_List" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets all records from the CourseOffering table
-- Table Comment: A CourseOffering is an offering of a particular course with a specific start date and end date.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				SELECT
					[CourseOfferingID],
					[CourseID],
					[TermStart],
					[TermEnd],
					[PassMark],
					[Active],
					[CoordinatorID]
				FROM
					[dbo].[CourseOffering]
					
				SELECT @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="CourseOffering_GetPaged" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the CourseOffering table passing page index and page count parameters
-- Table Comment: A CourseOffering is an offering of a particular course with a specific start date and end date.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[CourseOfferingID]'
				END

				-- SQL Server 2005 Paging
				DECLARE @SQL AS nvarchar(MAX)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [CourseOfferingID]'
				SET @SQL = @SQL + ', [CourseID]'
				SET @SQL = @SQL + ', [TermStart]'
				SET @SQL = @SQL + ', [TermEnd]'
				SET @SQL = @SQL + ', [PassMark]'
				SET @SQL = @SQL + ', [Active]'
				SET @SQL = @SQL + ', [CoordinatorID]'
				SET @SQL = @SQL + ' FROM [dbo].[CourseOffering]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [CourseOfferingID],'
				SET @SQL = @SQL + ' [CourseID],'
				SET @SQL = @SQL + ' [TermStart],'
				SET @SQL = @SQL + ' [TermEnd],'
				SET @SQL = @SQL + ' [PassMark],'
				SET @SQL = @SQL + ' [Active],'
				SET @SQL = @SQL + ' [CoordinatorID]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				EXEC sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[CourseOffering]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="CourseOffering_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Inserts a record into the CourseOffering table
-- Table Comment: A CourseOffering is an offering of a particular course with a specific start date and end date.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@CourseOfferingId" type="int" direction="Output" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@CourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@TermStart" type="datetime" direction="Input" size="8" precision="23" scale="3" param="" nulldefault=""/>
				<parameter name="@TermEnd" type="datetime" direction="Input" size="8" precision="23" scale="3" param="" nulldefault=""/>
				<parameter name="@PassMark" type="tinyint" direction="Input" size="1" precision="3" scale="0" param="" nulldefault=""/>
				<parameter name="@Active" type="bit" direction="Input" size="1" precision="1" scale="0" param="" nulldefault=""/>
				<parameter name="@CoordinatorId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				INSERT INTO [dbo].[CourseOffering]
					(
					[CourseID]
					,[TermStart]
					,[TermEnd]
					,[PassMark]
					,[Active]
					,[CoordinatorID]
					)
				VALUES
					(
					@CourseId
					,@TermStart
					,@TermEnd
					,@PassMark
					,@Active
					,@CoordinatorId
					)
				
				-- Get the identity value
				SET @CourseOfferingId = SCOPE_IDENTITY()
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="CourseOffering_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Updates a record in the CourseOffering table
-- Table Comment: A CourseOffering is an offering of a particular course with a specific start date and end date.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@CourseOfferingId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@CourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@TermStart" type="datetime" direction="Input" size="8" precision="23" scale="3" param="" nulldefault=""/>
				<parameter name="@TermEnd" type="datetime" direction="Input" size="8" precision="23" scale="3" param="" nulldefault=""/>
				<parameter name="@PassMark" type="tinyint" direction="Input" size="1" precision="3" scale="0" param="" nulldefault=""/>
				<parameter name="@Active" type="bit" direction="Input" size="1" precision="1" scale="0" param="" nulldefault=""/>
				<parameter name="@CoordinatorId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[CourseOffering]
				SET
					[CourseID] = @CourseId
					,[TermStart] = @TermStart
					,[TermEnd] = @TermEnd
					,[PassMark] = @PassMark
					,[Active] = @Active
					,[CoordinatorID] = @CoordinatorId
				WHERE
[CourseOfferingID] = @CourseOfferingId 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="CourseOffering_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Deletes a record in the CourseOffering table
-- Table Comment: A CourseOffering is an offering of a particular course with a specific start date and end date.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@CourseOfferingId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[CourseOffering] WITH (ROWLOCK) 
				WHERE
					[CourseOfferingID] = @CourseOfferingId
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="CourseOffering_GetByCoordinatorId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the CourseOffering table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@CoordinatorId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				
				SELECT
					[CourseOfferingID],
					[CourseID],
					[TermStart],
					[TermEnd],
					[PassMark],
					[Active],
					[CoordinatorID]
				FROM
					[dbo].[CourseOffering]
				WHERE
					[CoordinatorID] = @CoordinatorId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="CourseOffering_GetByCourseId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the CourseOffering table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@CourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				
				SELECT
					[CourseOfferingID],
					[CourseID],
					[TermStart],
					[TermEnd],
					[PassMark],
					[Active],
					[CoordinatorID]
				FROM
					[dbo].[CourseOffering]
				WHERE
					[CourseID] = @CourseId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="CourseOffering_GetByCourseOfferingId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the CourseOffering table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@CourseOfferingId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[CourseOfferingID],
					[CourseID],
					[TermStart],
					[TermEnd],
					[PassMark],
					[Active],
					[CoordinatorID]
				FROM
					[dbo].[CourseOffering]
				WHERE
					[CourseOfferingID] = @CourseOfferingId
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="CourseOffering_Find" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Finds records in the CourseOffering table passing nullable parameters
-- Table Comment: A CourseOffering is an offering of a particular course with a specific start date and end date.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@CourseOfferingId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@CourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@TermStart" type="datetime" direction="Input" size="8" precision="23" scale="3" param="" nulldefault="null"/>
				<parameter name="@TermEnd" type="datetime" direction="Input" size="8" precision="23" scale="3" param="" nulldefault="null"/>
				<parameter name="@PassMark" type="tinyint" direction="Input" size="1" precision="3" scale="0" param="" nulldefault="null"/>
				<parameter name="@Active" type="bit" direction="Input" size="1" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@CoordinatorId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [CourseOfferingID]
	, [CourseID]
	, [TermStart]
	, [TermEnd]
	, [PassMark]
	, [Active]
	, [CoordinatorID]
    FROM
	[dbo].[CourseOffering]
    WHERE 
	 ([CourseOfferingID] = @CourseOfferingId OR @CourseOfferingId IS NULL)
	AND ([CourseID] = @CourseId OR @CourseId IS NULL)
	AND ([TermStart] = @TermStart OR @TermStart IS NULL)
	AND ([TermEnd] = @TermEnd OR @TermEnd IS NULL)
	AND ([PassMark] = @PassMark OR @PassMark IS NULL)
	AND ([Active] = @Active OR @Active IS NULL)
	AND ([CoordinatorID] = @CoordinatorId OR @CoordinatorId IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [CourseOfferingID]
	, [CourseID]
	, [TermStart]
	, [TermEnd]
	, [PassMark]
	, [Active]
	, [CoordinatorID]
    FROM
	[dbo].[CourseOffering]
    WHERE 
	 ([CourseOfferingID] = @CourseOfferingId AND @CourseOfferingId is not null)
	OR ([CourseID] = @CourseId AND @CourseId is not null)
	OR ([TermStart] = @TermStart AND @TermStart is not null)
	OR ([TermEnd] = @TermEnd AND @TermEnd is not null)
	OR ([PassMark] = @PassMark AND @PassMark is not null)
	OR ([Active] = @Active AND @Active is not null)
	OR ([CoordinatorID] = @CoordinatorId AND @CoordinatorId is not null)
	SELECT @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="CourseOffering_Find_Dynamic"  skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the CourseOffering table using a dynamically generated query.
-- Table Comment: A CourseOffering is an offering of a particular course with a specific start date and end date.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				BEGIN
				
				WITH PageIndex AS (
					SELECT TOP {3} row_number() over (order by {1}) AS RowIndex
					, [CourseOfferingID]
					, [CourseID]
					, [TermStart]
					, [TermEnd]
					, [PassMark]
					, [Active]
					, [CoordinatorID]
					FROM [dbo].[CourseOffering] {0}
				)
				SELECT
				       [CourseOfferingID],
				       [CourseID],
				       [TermStart],
				       [TermEnd],
				       [PassMark],
				       [Active],
				       [CoordinatorID]
				  FROM PageIndex
				 WHERE RowIndex > {2}
				   AND RowIndex <= {3}
				ORDER BY {1};
				

				-- get total count
				SELECT COUNT(*) AS TotalRowCount FROM [dbo].[CourseOffering] {0};
				
				END
			]]></body>
		</procedure>
		
				
		<procedure owner="dbo" name="CourseOffering_LookupBySectionName"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@Name" type="char" direction="Input" size="3" precision="0" scale="0" param="(3)" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	CO.[CourseOfferingID],
	CO.[CourseID],
	CO.[TermStart],
	CO.[TermEnd],
	CO.[PassMark],
	CO.[Active],
	CO.[CoordinatorID]
FROM CourseOffering CO
INNER JOIN Section S ON S.[CourseOfferingID] = CO.[CourseOfferingID]
WHERE
	S.[Name]=@Name
	

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="CourseOffering_ListAllInactive"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
			</parameters>
			<body><![CDATA[
				


SELECT
	[CourseOfferingID],
	[CourseID],
	[TermStart],
	[TermEnd],
	[PassMark],
	[Active],
	[CoordinatorID]
FROM CourseOffering
WHERE [Active] = 0

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="CourseOffering_ListAllActive"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
			</parameters>
			<body><![CDATA[
				


SELECT
	[CourseOfferingID],
	[CourseID],
	[TermStart],
	[TermEnd],
	[PassMark],
	[Active],
	[CoordinatorID]
FROM CourseOffering
WHERE [Active] = 1

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="CourseOffering_LookupBySection"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@SectionID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	CO.[CourseOfferingID],
	CO.[CourseID],
	CO.[TermStart],
	CO.[TermEnd],
	CO.[PassMark],
	CO.[Active],
	CO.[CoordinatorID]
FROM CourseOffering CO
INNER JOIN Section S ON S.[CourseOfferingID] = CO.[CourseOfferingID]
WHERE
	S.[SectionID]=@SectionID
	

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="CourseOffering_LookupByTerm"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@Term" type="smallint" direction="Input" size="2" precision="5" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	CO.[CourseOfferingID],
	CO.[CourseID],
	CO.[TermStart],
	CO.[TermEnd],
	CO.[PassMark],
	CO.[Active],
	CO.[CoordinatorID]
FROM CourseOffering CO
INNER JOIN Course C ON C.[CourseID] = CO.[CourseID]
WHERE
	C.[Term]=@Term
	

			]]></body>
					</procedure>
		
		<procedure owner="dbo" name="EnrolledStudent_Get_List" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets all records from the EnrolledStudent table
-- Table Comment: An EnrolledStudent is a Student who has enrolled in a specific Section for a specific CourseOffering.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				SELECT
					[EnrolledStudentID],
					[StudentID],
					[CurrentStatus],
					[SectionID],
					[FinalMarkAwarded]
				FROM
					[dbo].[EnrolledStudent]
					
				SELECT @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="EnrolledStudent_GetPaged" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the EnrolledStudent table passing page index and page count parameters
-- Table Comment: An EnrolledStudent is a Student who has enrolled in a specific Section for a specific CourseOffering.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[EnrolledStudentID]'
				END

				-- SQL Server 2005 Paging
				DECLARE @SQL AS nvarchar(MAX)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [EnrolledStudentID]'
				SET @SQL = @SQL + ', [StudentID]'
				SET @SQL = @SQL + ', [CurrentStatus]'
				SET @SQL = @SQL + ', [SectionID]'
				SET @SQL = @SQL + ', [FinalMarkAwarded]'
				SET @SQL = @SQL + ' FROM [dbo].[EnrolledStudent]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [EnrolledStudentID],'
				SET @SQL = @SQL + ' [StudentID],'
				SET @SQL = @SQL + ' [CurrentStatus],'
				SET @SQL = @SQL + ' [SectionID],'
				SET @SQL = @SQL + ' [FinalMarkAwarded]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				EXEC sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[EnrolledStudent]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="EnrolledStudent_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Inserts a record into the EnrolledStudent table
-- Table Comment: An EnrolledStudent is a Student who has enrolled in a specific Section for a specific CourseOffering.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@EnrolledStudentId" type="int" direction="Output" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@StudentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@CurrentStatus" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@SectionId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@FinalMarkAwarded" type="tinyint" direction="Input" size="1" precision="3" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				INSERT INTO [dbo].[EnrolledStudent]
					(
					[StudentID]
					,[CurrentStatus]
					,[SectionID]
					,[FinalMarkAwarded]
					)
				VALUES
					(
					@StudentId
					,@CurrentStatus
					,@SectionId
					,@FinalMarkAwarded
					)
				
				-- Get the identity value
				SET @EnrolledStudentId = SCOPE_IDENTITY()
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="EnrolledStudent_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Updates a record in the EnrolledStudent table
-- Table Comment: An EnrolledStudent is a Student who has enrolled in a specific Section for a specific CourseOffering.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@EnrolledStudentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@StudentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@CurrentStatus" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@SectionId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@FinalMarkAwarded" type="tinyint" direction="Input" size="1" precision="3" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[EnrolledStudent]
				SET
					[StudentID] = @StudentId
					,[CurrentStatus] = @CurrentStatus
					,[SectionID] = @SectionId
					,[FinalMarkAwarded] = @FinalMarkAwarded
				WHERE
[EnrolledStudentID] = @EnrolledStudentId 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="EnrolledStudent_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Deletes a record in the EnrolledStudent table
-- Table Comment: An EnrolledStudent is a Student who has enrolled in a specific Section for a specific CourseOffering.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@EnrolledStudentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[EnrolledStudent] WITH (ROWLOCK) 
				WHERE
					[EnrolledStudentID] = @EnrolledStudentId
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="EnrolledStudent_GetByCurrentStatus" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the EnrolledStudent table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@CurrentStatus" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				
				SELECT
					[EnrolledStudentID],
					[StudentID],
					[CurrentStatus],
					[SectionID],
					[FinalMarkAwarded]
				FROM
					[dbo].[EnrolledStudent]
				WHERE
					[CurrentStatus] = @CurrentStatus
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="EnrolledStudent_GetBySectionId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the EnrolledStudent table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SectionId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				
				SELECT
					[EnrolledStudentID],
					[StudentID],
					[CurrentStatus],
					[SectionID],
					[FinalMarkAwarded]
				FROM
					[dbo].[EnrolledStudent]
				WHERE
					[SectionID] = @SectionId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="EnrolledStudent_GetByStudentId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the EnrolledStudent table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@StudentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				
				SELECT
					[EnrolledStudentID],
					[StudentID],
					[CurrentStatus],
					[SectionID],
					[FinalMarkAwarded]
				FROM
					[dbo].[EnrolledStudent]
				WHERE
					[StudentID] = @StudentId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="EnrolledStudent_GetByEnrolledStudentId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the EnrolledStudent table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@EnrolledStudentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[EnrolledStudentID],
					[StudentID],
					[CurrentStatus],
					[SectionID],
					[FinalMarkAwarded]
				FROM
					[dbo].[EnrolledStudent]
				WHERE
					[EnrolledStudentID] = @EnrolledStudentId
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="EnrolledStudent_Find" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Finds records in the EnrolledStudent table passing nullable parameters
-- Table Comment: An EnrolledStudent is a Student who has enrolled in a specific Section for a specific CourseOffering.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@EnrolledStudentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@StudentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@CurrentStatus" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@SectionId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@FinalMarkAwarded" type="tinyint" direction="Input" size="1" precision="3" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [EnrolledStudentID]
	, [StudentID]
	, [CurrentStatus]
	, [SectionID]
	, [FinalMarkAwarded]
    FROM
	[dbo].[EnrolledStudent]
    WHERE 
	 ([EnrolledStudentID] = @EnrolledStudentId OR @EnrolledStudentId IS NULL)
	AND ([StudentID] = @StudentId OR @StudentId IS NULL)
	AND ([CurrentStatus] = @CurrentStatus OR @CurrentStatus IS NULL)
	AND ([SectionID] = @SectionId OR @SectionId IS NULL)
	AND ([FinalMarkAwarded] = @FinalMarkAwarded OR @FinalMarkAwarded IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [EnrolledStudentID]
	, [StudentID]
	, [CurrentStatus]
	, [SectionID]
	, [FinalMarkAwarded]
    FROM
	[dbo].[EnrolledStudent]
    WHERE 
	 ([EnrolledStudentID] = @EnrolledStudentId AND @EnrolledStudentId is not null)
	OR ([StudentID] = @StudentId AND @StudentId is not null)
	OR ([CurrentStatus] = @CurrentStatus AND @CurrentStatus is not null)
	OR ([SectionID] = @SectionId AND @SectionId is not null)
	OR ([FinalMarkAwarded] = @FinalMarkAwarded AND @FinalMarkAwarded is not null)
	SELECT @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="EnrolledStudent_Find_Dynamic"  skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the EnrolledStudent table using a dynamically generated query.
-- Table Comment: An EnrolledStudent is a Student who has enrolled in a specific Section for a specific CourseOffering.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				BEGIN
				
				WITH PageIndex AS (
					SELECT TOP {3} row_number() over (order by {1}) AS RowIndex
					, [EnrolledStudentID]
					, [StudentID]
					, [CurrentStatus]
					, [SectionID]
					, [FinalMarkAwarded]
					FROM [dbo].[EnrolledStudent] {0}
				)
				SELECT
				       [EnrolledStudentID],
				       [StudentID],
				       [CurrentStatus],
				       [SectionID],
				       [FinalMarkAwarded]
				  FROM PageIndex
				 WHERE RowIndex > {2}
				   AND RowIndex <= {3}
				ORDER BY {1};
				

				-- get total count
				SELECT COUNT(*) AS TotalRowCount FROM [dbo].[EnrolledStudent] {0};
				
				END
			]]></body>
		</procedure>
		
				
		<procedure owner="dbo" name="EnrolledStudent_LookupBySectionNameAndCourseNumber"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@SectionName" type="char" direction="Input" size="3" precision="0" scale="0" param="(3)" nulldefault="null"/>
				<parameter name="@CourseNumber" type="varchar" direction="Input" size="50" precision="0" scale="0" param="(50)" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	ES.[EnrolledStudentID],
	ES.[StudentID],
	ES.[CurrentStatus],
	ES.[SectionID],
	ES.[FinalMarkAwarded]
FROM EnrolledStudent ES
INNER JOIN EnrollmentStatus ESTATUS ON ESTATUS.EnrollmentStatusID = ES.CurrentStatus
INNER JOIN SectionOffering SO ON SO.SectionID = ES.SectionID
INNER JOIN CourseOffering CO ON CO.CourseOfferingID = SO.CourseOfferingID
INNER JOIN Course C ON C.CourseID = CO.CourseID
WHERE
	SO.[Name]=@SectionName AND
	C.[Number]=@CourseNumber AND
	ESTATUS.[Description]='Enrolled' AND
	CO.[Active] = 1

			]]></body>
					</procedure>
		
		<procedure owner="dbo" name="CoursePrerequisite_Get_List" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets all records from the CoursePrerequisite table
-- Table Comment: CoursePrerequisites identify which given courses require another course to be completed before students are typically enrolled.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				SELECT
					[ID],
					[PrimaryCourseID],
					[RequiredCourseID]
				FROM
					[dbo].[CoursePrerequisite]
					
				SELECT @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="CoursePrerequisite_GetPaged" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the CoursePrerequisite table passing page index and page count parameters
-- Table Comment: CoursePrerequisites identify which given courses require another course to be completed before students are typically enrolled.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[ID]'
				END

				-- SQL Server 2005 Paging
				DECLARE @SQL AS nvarchar(MAX)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [ID]'
				SET @SQL = @SQL + ', [PrimaryCourseID]'
				SET @SQL = @SQL + ', [RequiredCourseID]'
				SET @SQL = @SQL + ' FROM [dbo].[CoursePrerequisite]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [ID],'
				SET @SQL = @SQL + ' [PrimaryCourseID],'
				SET @SQL = @SQL + ' [RequiredCourseID]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				EXEC sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[CoursePrerequisite]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="CoursePrerequisite_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Inserts a record into the CoursePrerequisite table
-- Table Comment: CoursePrerequisites identify which given courses require another course to be completed before students are typically enrolled.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Output" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@PrimaryCourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@RequiredCourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				INSERT INTO [dbo].[CoursePrerequisite]
					(
					[PrimaryCourseID]
					,[RequiredCourseID]
					)
				VALUES
					(
					@PrimaryCourseId
					,@RequiredCourseId
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="CoursePrerequisite_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Updates a record in the CoursePrerequisite table
-- Table Comment: CoursePrerequisites identify which given courses require another course to be completed before students are typically enrolled.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@PrimaryCourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@RequiredCourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[CoursePrerequisite]
				SET
					[PrimaryCourseID] = @PrimaryCourseId
					,[RequiredCourseID] = @RequiredCourseId
				WHERE
[ID] = @Id 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="CoursePrerequisite_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Deletes a record in the CoursePrerequisite table
-- Table Comment: CoursePrerequisites identify which given courses require another course to be completed before students are typically enrolled.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[CoursePrerequisite] WITH (ROWLOCK) 
				WHERE
					[ID] = @Id
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="CoursePrerequisite_GetByPrimaryCourseId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the CoursePrerequisite table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@PrimaryCourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				
				SELECT
					[ID],
					[PrimaryCourseID],
					[RequiredCourseID]
				FROM
					[dbo].[CoursePrerequisite]
				WHERE
					[PrimaryCourseID] = @PrimaryCourseId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="CoursePrerequisite_GetByRequiredCourseId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the CoursePrerequisite table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@RequiredCourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				
				SELECT
					[ID],
					[PrimaryCourseID],
					[RequiredCourseID]
				FROM
					[dbo].[CoursePrerequisite]
				WHERE
					[RequiredCourseID] = @RequiredCourseId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="CoursePrerequisite_GetById" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the CoursePrerequisite table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[ID],
					[PrimaryCourseID],
					[RequiredCourseID]
				FROM
					[dbo].[CoursePrerequisite]
				WHERE
					[ID] = @Id
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="CoursePrerequisite_GetByPrimaryCourseIdRequiredCourseId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the CoursePrerequisite table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@PrimaryCourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@RequiredCourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[ID],
					[PrimaryCourseID],
					[RequiredCourseID]
				FROM
					[dbo].[CoursePrerequisite]
				WHERE
					[PrimaryCourseID] = @PrimaryCourseId
					AND [RequiredCourseID] = @RequiredCourseId
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="CoursePrerequisite_Find" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Finds records in the CoursePrerequisite table passing nullable parameters
-- Table Comment: CoursePrerequisites identify which given courses require another course to be completed before students are typically enrolled.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@Id" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@PrimaryCourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@RequiredCourseId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [ID]
	, [PrimaryCourseID]
	, [RequiredCourseID]
    FROM
	[dbo].[CoursePrerequisite]
    WHERE 
	 ([ID] = @Id OR @Id IS NULL)
	AND ([PrimaryCourseID] = @PrimaryCourseId OR @PrimaryCourseId IS NULL)
	AND ([RequiredCourseID] = @RequiredCourseId OR @RequiredCourseId IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [ID]
	, [PrimaryCourseID]
	, [RequiredCourseID]
    FROM
	[dbo].[CoursePrerequisite]
    WHERE 
	 ([ID] = @Id AND @Id is not null)
	OR ([PrimaryCourseID] = @PrimaryCourseId AND @PrimaryCourseId is not null)
	OR ([RequiredCourseID] = @RequiredCourseId AND @RequiredCourseId is not null)
	SELECT @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="CoursePrerequisite_Find_Dynamic"  skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the CoursePrerequisite table using a dynamically generated query.
-- Table Comment: CoursePrerequisites identify which given courses require another course to be completed before students are typically enrolled.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				BEGIN
				
				WITH PageIndex AS (
					SELECT TOP {3} row_number() over (order by {1}) AS RowIndex
					, [ID]
					, [PrimaryCourseID]
					, [RequiredCourseID]
					FROM [dbo].[CoursePrerequisite] {0}
				)
				SELECT
				       [ID],
				       [PrimaryCourseID],
				       [RequiredCourseID]
				  FROM PageIndex
				 WHERE RowIndex > {2}
				   AND RowIndex <= {3}
				ORDER BY {1};
				

				-- get total count
				SELECT COUNT(*) AS TotalRowCount FROM [dbo].[CoursePrerequisite] {0};
				
				END
			]]></body>
		</procedure>
		
				
		<procedure owner="dbo" name="CoursePrerequisite_LookupByPrimaryAndRequiredIDs"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@PrimaryCourseID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@RequiredCourseID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	[ID],
	[PrimaryCourseID],
	[RequiredCourseID]
FROM CoursePrerequisite
WHERE
	[PrimaryCourseID] = @PrimaryCourseID
AND [RequiredCourseID] = @RequiredCourseID
	

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="CoursePrerequisite_LookupByPrimaryAndRequiredNumbers"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@PrimaryCourseNumber" type="varchar" direction="Input" size="10" precision="0" scale="0" param="(10)" nulldefault="null"/>
				<parameter name="@RequiredCourseNumber" type="varchar" direction="Input" size="10" precision="0" scale="0" param="(10)" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	CP.[ID],
	CP.[PrimaryCourseID],
	CP.[RequiredCourseID]
FROM CoursePrerequisite CP
INNER JOIN Course C1 ON C1.[CourseID] = CP.[PrimaryCourseID]
INNER JOIN Course C2 ON C2.[CourseID] = CP.[RequiredCourseID]
WHERE
	C1.[Number] = @PrimaryCourseNumber
AND C2.[Number] = @RequiredCourseNumber
	

			]]></body>
					</procedure>
		
		<procedure owner="dbo" name="StudentMark_Get_List" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets all records from the StudentMark table
-- Table Comment: A StudentMark is the mark assigned to an individual student for a specific EvaluationComponent.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				SELECT
					[StudentMarkID],
					[StudentID],
					[EvaluationComponentID],
					[TotalPossibleMarks],
					[MarksEarned],
					[GradeAwarded],
					[Comments]
				FROM
					[dbo].[StudentMark]
					
				SELECT @@ROWCOUNT
			]]></body>
		</procedure>
		
		
		<procedure owner="dbo" name="StudentMark_GetPaged" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the StudentMark table passing page index and page count parameters
-- Table Comment: A StudentMark is the mark assigned to an individual student for a specific EvaluationComponent.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@WhereClause" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)" />
				<parameter name="@OrderBy" type="varchar" direction="Input" size="2000" precision="0" scale="0" param="(2000)"/>
				<parameter name="@PageIndex" type="int" direction="Input" size="0" precision="10" scale="0" />
				<parameter name="@PageSize" type="int" direction="Input" size="0" precision="10" scale="0" /> 
			</parameters>
			<body><![CDATA[
				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[StudentMarkID]'
				END

				-- SQL Server 2005 Paging
				DECLARE @SQL AS nvarchar(MAX)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [StudentMarkID]'
				SET @SQL = @SQL + ', [StudentID]'
				SET @SQL = @SQL + ', [EvaluationComponentID]'
				SET @SQL = @SQL + ', [TotalPossibleMarks]'
				SET @SQL = @SQL + ', [MarksEarned]'
				SET @SQL = @SQL + ', [GradeAwarded]'
				SET @SQL = @SQL + ', [Comments]'
				SET @SQL = @SQL + ' FROM [dbo].[StudentMark]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [StudentMarkID],'
				SET @SQL = @SQL + ' [StudentID],'
				SET @SQL = @SQL + ' [EvaluationComponentID],'
				SET @SQL = @SQL + ' [TotalPossibleMarks],'
				SET @SQL = @SQL + ' [MarksEarned],'
				SET @SQL = @SQL + ' [GradeAwarded],'
				SET @SQL = @SQL + ' [Comments]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				EXEC sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[StudentMark]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			]]></body>
		</procedure>
		
		<procedure owner="dbo" name="StudentMark_Insert" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Inserts a record into the StudentMark table
-- Table Comment: A StudentMark is the mark assigned to an individual student for a specific EvaluationComponent.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@StudentMarkId" type="int" direction="Output" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@StudentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@EvaluationComponentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@TotalPossibleMarks" type="decimal" direction="Input" size="9" precision="18" scale="0" param="(18, 0)" nulldefault=""/>
				<parameter name="@MarksEarned" type="decimal" direction="Input" size="9" precision="18" scale="0" param="(18, 0)" nulldefault=""/>
				<parameter name="@GradeAwarded" type="decimal" direction="Input" size="9" precision="18" scale="0" param="(18, 0)" nulldefault=""/>
				<parameter name="@Comments" type="varchar" direction="Input" size="100" precision="0" scale="0" param="(100)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				INSERT INTO [dbo].[StudentMark]
					(
					[StudentID]
					,[EvaluationComponentID]
					,[TotalPossibleMarks]
					,[MarksEarned]
					,[GradeAwarded]
					,[Comments]
					)
				VALUES
					(
					@StudentId
					,@EvaluationComponentId
					,@TotalPossibleMarks
					,@MarksEarned
					,@GradeAwarded
					,@Comments
					)
				
				-- Get the identity value
				SET @StudentMarkId = SCOPE_IDENTITY()
									
							
			]]></body>
		</procedure>
		<procedure owner="dbo" name="StudentMark_Update" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Updates a record in the StudentMark table
-- Table Comment: A StudentMark is the mark assigned to an individual student for a specific EvaluationComponent.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@StudentMarkId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@StudentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@EvaluationComponentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
				<parameter name="@TotalPossibleMarks" type="decimal" direction="Input" size="9" precision="18" scale="0" param="(18, 0)" nulldefault=""/>
				<parameter name="@MarksEarned" type="decimal" direction="Input" size="9" precision="18" scale="0" param="(18, 0)" nulldefault=""/>
				<parameter name="@GradeAwarded" type="decimal" direction="Input" size="9" precision="18" scale="0" param="(18, 0)" nulldefault=""/>
				<parameter name="@Comments" type="varchar" direction="Input" size="100" precision="0" scale="0" param="(100)" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[StudentMark]
				SET
					[StudentID] = @StudentId
					,[EvaluationComponentID] = @EvaluationComponentId
					,[TotalPossibleMarks] = @TotalPossibleMarks
					,[MarksEarned] = @MarksEarned
					,[GradeAwarded] = @GradeAwarded
					,[Comments] = @Comments
				WHERE
[StudentMarkID] = @StudentMarkId 
				
			]]></body>
		</procedure>
		<procedure owner="dbo" name="StudentMark_Delete" grant="">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Deletes a record in the StudentMark table
-- Table Comment: A StudentMark is the mark assigned to an individual student for a specific EvaluationComponent.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@StudentMarkId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				DELETE FROM [dbo].[StudentMark] WITH (ROWLOCK) 
				WHERE
					[StudentMarkID] = @StudentMarkId
					
			]]></body>
		</procedure>
		<procedure owner="dbo" name="StudentMark_GetByEvaluationComponentId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the StudentMark table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@EvaluationComponentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				
				SELECT
					[StudentMarkID],
					[StudentID],
					[EvaluationComponentID],
					[TotalPossibleMarks],
					[MarksEarned],
					[GradeAwarded],
					[Comments]
				FROM
					[dbo].[StudentMark]
				WHERE
					[EvaluationComponentID] = @EvaluationComponentId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="StudentMark_GetByStudentId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the StudentMark table through a foreign key
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@StudentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SET ANSI_NULLS OFF
				
				SELECT
					[StudentMarkID],
					[StudentID],
					[EvaluationComponentID],
					[TotalPossibleMarks],
					[MarksEarned],
					[GradeAwarded],
					[Comments]
				FROM
					[dbo].[StudentMark]
				WHERE
					[StudentID] = @StudentId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			]]></body>
		</procedure>
		<procedure owner="dbo" name="StudentMark_GetByStudentMarkId" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Select records from the StudentMark table through an index
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@StudentMarkId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault=""/>
			</parameters>
			<body><![CDATA[
				SELECT
					[StudentMarkID],
					[StudentID],
					[EvaluationComponentID],
					[TotalPossibleMarks],
					[MarksEarned],
					[GradeAwarded],
					[Comments]
				FROM
					[dbo].[StudentMark]
				WHERE
					[StudentMarkID] = @StudentMarkId
				SELECT @@ROWCOUNT
					
			]]></body>
			
		</procedure>
		<procedure owner="dbo" name="StudentMark_Find" >
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Finds records in the StudentMark table passing nullable parameters
-- Table Comment: A StudentMark is the mark assigned to an individual student for a specific EvaluationComponent.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters>
				<parameter name="@SearchUsingOR" type="bit" direction="Input" size="0" precision="1" scale="0" param="" nulldefault="null"/>
				<parameter name="@StudentMarkId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@StudentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@EvaluationComponentId" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@TotalPossibleMarks" type="decimal" direction="Input" size="9" precision="18" scale="0" param="(18, 0)" nulldefault="null"/>
				<parameter name="@MarksEarned" type="decimal" direction="Input" size="9" precision="18" scale="0" param="(18, 0)" nulldefault="null"/>
				<parameter name="@GradeAwarded" type="decimal" direction="Input" size="9" precision="18" scale="0" param="(18, 0)" nulldefault="null"/>
				<parameter name="@Comments" type="varchar" direction="Input" size="100" precision="0" scale="0" param="(100)" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [StudentMarkID]
	, [StudentID]
	, [EvaluationComponentID]
	, [TotalPossibleMarks]
	, [MarksEarned]
	, [GradeAwarded]
	, [Comments]
    FROM
	[dbo].[StudentMark]
    WHERE 
	 ([StudentMarkID] = @StudentMarkId OR @StudentMarkId IS NULL)
	AND ([StudentID] = @StudentId OR @StudentId IS NULL)
	AND ([EvaluationComponentID] = @EvaluationComponentId OR @EvaluationComponentId IS NULL)
	AND ([TotalPossibleMarks] = @TotalPossibleMarks OR @TotalPossibleMarks IS NULL)
	AND ([MarksEarned] = @MarksEarned OR @MarksEarned IS NULL)
	AND ([GradeAwarded] = @GradeAwarded OR @GradeAwarded IS NULL)
	AND ([Comments] = @Comments OR @Comments IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [StudentMarkID]
	, [StudentID]
	, [EvaluationComponentID]
	, [TotalPossibleMarks]
	, [MarksEarned]
	, [GradeAwarded]
	, [Comments]
    FROM
	[dbo].[StudentMark]
    WHERE 
	 ([StudentMarkID] = @StudentMarkId AND @StudentMarkId is not null)
	OR ([StudentID] = @StudentId AND @StudentId is not null)
	OR ([EvaluationComponentID] = @EvaluationComponentId AND @EvaluationComponentId is not null)
	OR ([TotalPossibleMarks] = @TotalPossibleMarks AND @TotalPossibleMarks is not null)
	OR ([MarksEarned] = @MarksEarned AND @MarksEarned is not null)
	OR ([GradeAwarded] = @GradeAwarded AND @GradeAwarded is not null)
	OR ([Comments] = @Comments AND @Comments is not null)
	SELECT @@ROWCOUNT			
  END
				]]></body>
		</procedure>

		
		<procedure owner="dbo" name="StudentMark_Find_Dynamic"  skip="true">
			<comment><![CDATA[/*
----------------------------------------------------------------------------------------------------
-- Date Created: May-02-09

-- Created By: Dynamic Generation Inc. (http://www.DynamicGeneration.com)
-- Purpose: Gets records from the StudentMark table using a dynamically generated query.
-- Table Comment: A StudentMark is the mark assigned to an individual student for a specific EvaluationComponent.
----------------------------------------------------------------------------------------------------
*/
]]></comment>
			<parameters/>
			<body><![CDATA[
				
				BEGIN
				
				WITH PageIndex AS (
					SELECT TOP {3} row_number() over (order by {1}) AS RowIndex
					, [StudentMarkID]
					, [StudentID]
					, [EvaluationComponentID]
					, [TotalPossibleMarks]
					, [MarksEarned]
					, [GradeAwarded]
					, [Comments]
					FROM [dbo].[StudentMark] {0}
				)
				SELECT
				       [StudentMarkID],
				       [StudentID],
				       [EvaluationComponentID],
				       [TotalPossibleMarks],
				       [MarksEarned],
				       [GradeAwarded],
				       [Comments]
				  FROM PageIndex
				 WHERE RowIndex > {2}
				   AND RowIndex <= {3}
				ORDER BY {1};
				

				-- get total count
				SELECT COUNT(*) AS TotalRowCount FROM [dbo].[StudentMark] {0};
				
				END
			]]></body>
		</procedure>
		
				
		<procedure owner="dbo" name="StudentMark_LookupByStudentAndComponent"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@StudentID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@ComponentID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	[StudentMarkID],
	[StudentID],
	[EvaluationComponentID],
	[GradeAwarded],
	[TotalPossibleMarks],
	[MarksEarned],
	[Comments]
FROM StudentMark
WHERE
	[StudentID] = @StudentID
AND [EvaluationComponentID] = @ComponentID
	

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="StudentMark_LookupActiveByCourseAndSection"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@CourseID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
				<parameter name="@SectionID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	S.[StudentMarkID],
	S.[StudentID],
	S.[EvaluationComponentID],
	S.[GradeAwarded],
	S.[TotalPossibleMarks],
	S.[MarksEarned],
	S.[Comments]
FROM StudentMark S
INNER JOIN EvaluationComponent EC ON EC.EvaluationComponentID = S.EvaluationComponentID
INNER JOIN CourseOffering CO ON CO.CourseOfferingID = EC.CourseOfferingID
INNER JOIN Section SE ON SE.CourseOfferingID = CO.CourseOfferingID
WHERE	CO.Active = 1
AND		CO.CourseID = @CourseID
AND		SE.SectionID = @SectionID

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="StudentMark_ListAllActive"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
			</parameters>
			<body><![CDATA[
				


SELECT
	S.[StudentMarkID],
	S.[StudentID],
	S.[EvaluationComponentID],
	S.[GradeAwarded],
	S.[TotalPossibleMarks],
	S.[MarksEarned],
	S.[Comments]
FROM StudentMark S
INNER JOIN EvaluationComponent EC ON EC.EvaluationComponentID = S.EvaluationComponentID
INNER JOIN CourseOffering CO ON CO.CourseOfferingID = EC.CourseOfferingID
WHERE	CO.Active = 1

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="StudentMark_LookupActiveBySection"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@SectionID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	S.[StudentMarkID],
	S.[StudentID],
	S.[EvaluationComponentID],
	S.[GradeAwarded],
	S.[TotalPossibleMarks],
	S.[MarksEarned],
	S.[Comments]
FROM StudentMark S
INNER JOIN EvaluationComponent EC ON EC.EvaluationComponentID = S.EvaluationComponentID
INNER JOIN CourseOffering CO ON CO.CourseOfferingID = EC.CourseOfferingID
INNER JOIN Section SE ON SE.CourseOfferingID = CO.CourseOfferingID
WHERE	CO.Active = 1
AND		SE.SectionID = @SectionID

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="StudentMark_LookupActiveByCourse"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@CourseID" type="int" direction="Input" size="4" precision="10" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	S.[StudentMarkID],
	S.[StudentID],
	S.[EvaluationComponentID],
	S.[GradeAwarded],
	S.[TotalPossibleMarks],
	S.[MarksEarned],
	S.[Comments]
FROM StudentMark S
INNER JOIN EvaluationComponent EC ON EC.EvaluationComponentID = S.EvaluationComponentID
INNER JOIN CourseOffering CO ON CO.CourseOfferingID = EC.CourseOfferingID
WHERE	CO.Active = 1
AND		CO.CourseID = @CourseID

			]]></body>
					</procedure>
				
		<procedure owner="dbo" name="StudentMark_LookupActiveByTerm"  skip="true">
			<comment><![CDATA[
]]></comment>
			<parameters>
				<parameter name="@Term" type="smallint" direction="Input" size="2" precision="5" scale="0" param="" nulldefault="null"/>
			</parameters>
			<body><![CDATA[
				


SELECT
	S.[StudentMarkID],
	S.[StudentID],
	S.[EvaluationComponentID],
	S.[GradeAwarded],
	S.[TotalPossibleMarks],
	S.[MarksEarned],
	S.[Comments]
FROM StudentMark S
INNER JOIN EvaluationComponent EC ON EC.EvaluationComponentID = S.EvaluationComponentID
INNER JOIN CourseOffering CO ON CO.CourseOfferingID = EC.CourseOfferingID
INNER JOIN Course C ON C.CourseID = CO.CourseID
WHERE	CO.Active = 1
AND		C.Term = @Term

			]]></body>
					</procedure>
</procedures>
</root>
